import pymysql, time, requests, json
from pymysql import cursors
from pyquery import PyQuery as pq


class ShareMysql:
    def __init__(self, DbHost, username, password, dbname):
        self.cursors = cursors
        self.username = username
        self.password = password
        self.dbname = dbname
        self.DbHost = DbHost
        self.conn = pymysql.connect
        self.mysqlCon()

    def mysqlCon(self):
        conn = pymysql.connect(
            host=self.DbHost,
            port=3306,
            user=self.username,
            password=self.password,
            db=self.dbname,
            charset='utf8'
        )
        return conn

    def getOne(self, sql=str):
        conn = self.mysqlCon()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql)
        res = cursor.fetchone()
        return res

    def getlist(self, sql=str):
        conn = self.mysqlCon()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        cursor.execute(sql)
        res = cursor.fetchall()
        return res

    def cursorssds(self, sql=str):
        conn = self.mysqlCon()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        res = cursor.execute(sql)
        conn.commit()
        conn.close()
        return res

    def insert(self, tableName=str, data=dict):
        key = []
        keyValue = []
        value = []
        for h in data:
            key.append('`' + h + '`')
            keyValue.append('%s')
            value.append("'" + str(data[h]).strip() + "'")
        shr = ','.join(key)
        shrD = ','.join(value)
        sql = "insert into `" + tableName + "`(" + shr + ") values ( " + shrD + " )"
        return self.cursorssds(sql)

    def find(self, tableName=str, where="1=1"):
        sql = "select * from `" + tableName + "`" + " where " + str(where) + " limit 1 "
        return self.getOne(sql)

    def List(self, tableName=str, where="1=1", page=1, pageSize=100):
        start = (page - 1) * pageSize
        sql = "select * from `" + tableName + "`" + " where " + str(where) + " limit " + str(start) + "," + str(
            pageSize)
        return self.getlist(sql)

    def update(self, tableName=str, data=dict, where=None):
        strS = ""
        for j in data:
            strS += "`" + j + "`='" + data[j] + "',"
        strS = str(strS)
        strS = strS.rstrip(',')
        sql = "update `" + tableName + "` set  " + strS + " where " + str(where)
        return self.cursorssds(sql)

    def delete(self, tableName=str, where=None):
        sql = "delete from `" + tableName + "`" + "  where " + str(where)
        return self.cursorssds(sql)


class TripInfo:
    def __init__(self):

        pass

    def mysqlConn(self):
        conn = ShareMysql(DbHost="127.0.0.1", username="root", password="root", dbname="blog")
        return conn

    def setCountry(self):

        doc = pq(url="https://place.qyer.com/", encoding='utf-8')
        country = doc("#js-container-4 ul")
        for j in country:
            oneArr = pq(j).find("li")
            for k in oneArr:
                pqk = pq(k).find("a")
                data = {
                    "name": pqk.find("span").text(),
                    "ename": pqk.find("em").text(),
                    "url": pqk.attr("href"),
                    "dname": self.toLowerCase(pqk.find("em").text())
                }
                print(data)
                try:
                    self.mysqlConn().insert("trip_country", data)
                    pass
                except:
                    print("contryList,error")
                    pass
        pass

    def getCountryList(self):
        contryList = self.mysqlConn().getlist("select * from trip_country where pid=0")
        for item in contryList:
            try:
                self.getCityList(item)
            except:
                pass

        print(contryList)

    def selectCityList(self):
        cityList = self.mysqlConn().getlist("select * from trip_city where spid=0 order by id desc limit 2000")
        for item in cityList:
            try:
                if item['spid'] != 0:
                    continue
                self.getsScenicList(item)
                sql = "update `trip_country` set status=1  where id= " + str(item['id'])
                self.mysqlConn().cursorssds(sql)
            except:
                pass

    def getPid(self, html=str, sp="'ugc_content':'"):
        s = html.split(sp)
        b = s[1].split("',")
        pids = int(b[0])
        return pids

    def getsScenicList(self, dataInfo=dict):
        url = "https:" + dataInfo["url"] + "sight/"
        doc = pq(url=url, encoding='utf-8')
        html = doc.html()
        try:
            pids = self.getPid(html)
            if dataInfo['spid'] == 0:
                print(211221)
                dataInfo['spid'] = pids
                sql = "update `trip_city` set  spid=" + str(pids) + " where id= " + str(dataInfo['id'])
                self.mysqlConn().cursorssds(sql)
        except:
            return
        listp = doc(".ui_page").find("a")
        if listp.length > 0:
            pageNum = pq(listp[listp.length - 2]).attr("data-page")
        else:
            pageNum = 1
        for page in range(1, int(pageNum) + 1):
            content = requests.post(url="https://place.qyer.com/poi.php?action=list_json", data={
                "type": "city",
                "pid": pids,
                "page": page,
                "sort": 32
            }).content
            res = json.loads(content)
            contentList = list(res['data']["list"])
            if len(contentList) == 0:
                continue
            for j in contentList:
                rscount = self.mysqlConn().find("trip_content", "oid=" + j["id"])
                if rscount is not None:
                    continue
                j['datainfo'] = dataInfo
                self.getDetaiInfo(j)

    def getDetaiInfo(self, data):
        url = "https:" + data["url"]
        doc = pq(url=url, encoding='utf-8')
        print(url)
        keywords = doc("meta[name = 'keywords']").text()
        coverphoto = doc(".coverphoto").find("img").attr("src")
        content = doc(".compo-detail-info .poi-detail").find("div").html()
        photoNum = doc(".coverphoto").find("span").find("em").text()

        saveData = {
            "oid": data["id"],
            'catename': data['catename'],
            'keywords': keywords,
            'coverphoto': coverphoto,
            'cnname': data['cnname'],
            'enname': data['enname'],
            'grade': data['grade'],
            'hotgrade': data['hotgrade'],
            'url': data['url'],
            'comment_url': data['commentUrl'],
            'content': content,
            "pid": data["datainfo"]["pid"],
            "spid": data["datainfo"]["spid"],
        }
        try:
            res = self.mysqlConn().insert("trip_content", saveData)
            print("mysql", res, saveData)
        except:
            pass
        try:
            if int(photoNum) > 0:
                pass
        except:
            pass

    def getCityList(self, data):
        urls = data['url'] + "citylist-1-0-1/"
        doc = pq(url=urls, encoding='utf-8')
        pid = self.getPid(doc.html(), "'ugc_content':'")
        if data['pid'] == 0:
            sql = "update `trip_country` set  pid=" + str(pid) + " where id= " + str(data['id'])
            self.mysqlConn().cursorssds(sql)
            data['pid'] = pid
        pageList = doc(".ui_page").find("a")
        print(int(pageList.length))
        if pageList.length > 0:
            leng = pageList.length
            listPage = pq(pageList[leng - 2]).attr("data-page")
        else:
            listPage = 1
        for page in range(1, int(listPage) + 1):
            urlsr = data['url'] + "citylist-1-0-" + str(page) + "/"
            self.getCountryCityListPage(urlsr, data)
        return pid

    def getCountryCityListPage(self, url, dataInfo):  # 获取城市列表
        doc = pq(url=url, encoding='utf-8')
        listInfo = doc(".plcCityWordlist").find("li")
        for j in listInfo:
            oneDoc = pq(j).find("a")
            OoneDoc = pq(j).find(".en").text()
            oneDoc.remove(".en")
            data = {
                "url": oneDoc.attr("href"),
                "name": str(oneDoc.text()).strip(),
                "ename": self.toLowerCase(str(OoneDoc).replace(" ", "")),
                "pid": dataInfo['pid']
            }
            try:
                res = self.mysqlConn().insert("trip_city", data)
                print(res)
            except:
                pass
            print(data)

    def toLowerCase(self, str):
        strList = list(str)
        for i, j in enumerate(strList):
            if 'A' <= j <= 'Z':
                strList[i] = chr(ord(j) + ord('a') - ord('A'))
        return "".join(strList)


# TripInfo().setCountry()
# TripInfo().getCountryList()
TripInfo().selectCityList()
